{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f22eab3f84cbeb37",
   "metadata": {},
   "source": [
    "# SQL (SQLAlchemy)\n",
    "\n",
    ">[Structured Query Language (SQL)](https://en.wikipedia.org/wiki/SQL) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e., data incorporating relations among entities and variables.\n",
    "\n",
    ">[SQLAlchemy](https://github.com/sqlalchemy/sqlalchemy) is an open-source `SQL` toolkit and object-relational mapper (ORM) for the Python programming language released under the MIT License.\n",
    "\n",
    "This notebook goes over a `SQLChatMessageHistory` class that allows to store chat history in any database supported by `SQLAlchemy`.\n",
    "\n",
    "Please note that to use it with databases other than `SQLite`, you will need to install the corresponding database driver."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da400c79-a360-43e2-be60-401fd02b2819",
   "metadata": {},
   "source": [
    "## Setup\n",
    "\n",
    "The integration lives in the `langchain-community` package, so we need to install that. We also need to install the `SQLAlchemy` package.\n",
    "\n",
    "```bash\n",
    "pip install -U langchain-community SQLAlchemy langchain-openai\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70ed8354-ce96-46b5-a580-4cb7aac5c3d0",
   "metadata": {},
   "source": [
    "It's also helpful (but not needed) to set up [LangSmith](https://smith.langchain.com/) for best-in-class observability"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0976204d-c681-4288-bfe5-a550e0340f35",
   "metadata": {},
   "outputs": [],
   "source": [
    "# os.environ[\"LANGCHAIN_TRACING_V2\"] = \"true\"\n",
    "# os.environ[\"LANGCHAIN_API_KEY\"] = getpass.getpass()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8f2830ee9ca1e01",
   "metadata": {},
   "source": [
    "## Usage\n",
    "\n",
    "To use the storage you need to provide only 2 things:\n",
    "\n",
    "1. Session Id - a unique identifier of the session, like user name, email, chat id etc.\n",
    "2. Connection string - a string that specifies the database connection. It will be passed to SQLAlchemy create_engine function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4576e914a866fb40",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-08-28T10:04:38.077748Z",
     "start_time": "2023-08-28T10:04:36.105894Z"
    },
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from langchain_community.chat_message_histories import SQLChatMessageHistory\n",
    "\n",
    "chat_message_history = SQLChatMessageHistory(\n",
    "    session_id=\"test_session\", connection_string=\"sqlite:///sqlite.db\"\n",
    ")\n",
    "\n",
    "chat_message_history.add_user_message(\"Hello\")\n",
    "chat_message_history.add_ai_message(\"Hi\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b476688cbb32ba90",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-08-28T10:04:38.929396Z",
     "start_time": "2023-08-28T10:04:38.915727Z"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[HumanMessage(content='Hello'), AIMessage(content='Hi')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chat_message_history.messages"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e5337719d5614fd",
   "metadata": {},
   "source": [
    "## Chaining\n",
    "\n",
    "We can easily combine this message history class with [LCEL Runnables](/docs/expression_language/how_to/message_history)\n",
    "\n",
    "To do this we will want to use OpenAI, so we need to install that\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6558418b-0ece-4d01-9661-56d562d78f7a",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder\n",
    "from langchain_core.runnables.history import RunnableWithMessageHistory\n",
    "from langchain_openai import ChatOpenAI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "82149122-61d3-490d-9bdb-bb98606e8ba1",
   "metadata": {},
   "outputs": [],
   "source": [
    "prompt = ChatPromptTemplate.from_messages(\n",
    "    [\n",
    "        (\"system\", \"You are a helpful assistant.\"),\n",
    "        MessagesPlaceholder(variable_name=\"history\"),\n",
    "        (\"human\", \"{question}\"),\n",
    "    ]\n",
    ")\n",
    "\n",
    "chain = prompt | ChatOpenAI()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2df90853-b67c-490f-b7f8-b69d69270b9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "chain_with_history = RunnableWithMessageHistory(\n",
    "    chain,\n",
    "    lambda session_id: SQLChatMessageHistory(\n",
    "        session_id=session_id, connection_string=\"sqlite:///sqlite.db\"\n",
    "    ),\n",
    "    input_messages_key=\"question\",\n",
    "    history_messages_key=\"history\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0ce596b8-3b78-48fd-9f92-46dccbbfd58b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is where we configure the session id\n",
    "config = {\"configurable\": {\"session_id\": \"<SESSION_ID>\"}}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "38e1423b-ba86-4496-9151-25932fab1a8b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "AIMessage(content='Hello Bob! How can I assist you today?')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chain_with_history.invoke({\"question\": \"Hi! I'm bob\"}, config=config)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "2ee4ee62-a216-4fb1-bf33-57476a84cf16",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "AIMessage(content='Your name is Bob! Is there anything specific you would like assistance with, Bob?')"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chain_with_history.invoke({\"question\": \"Whats my name\"}, config=config)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
